
alter  trigger tt_billbatch_au
on dbo.tt_billbatch
for update
as  
begin
  set nocount on
  declare 
    @old_quantity decimal(18,4), @new_quantity decimal(18,4), 
    @old_assqty decimal(18,4), @new_assqty decimal(18,4),
    @old_depotid integer, @new_depotid integer,
    @old_productid integer, @new_productid integer,
    @old_batchno varchar(200),  @new_batchno varchar(200),
    @oproduceddate datetime, @ovaliddate datetime,
    @produceddate datetime, @validdate datetime,
     @ocustom1 varchar(200), @ocustom2 varchar(200), @ocustom3 varchar(200),
     @olength decimal(18,8), @owidth decimal(18,8), @oheight decimal(18,8),
     @custom1 varchar(200), @custom2 varchar(200), @custom3 varchar(200),
     @length decimal(18,8), @width decimal(18,8), @height decimal(18,8)
    
  declare del_cursor cursor local forward_only static optimistic for  
    select batchcode, bquantity, depotid, productid, assqty,
      productdate, validdate, custom1, custom2, custom3, length, width, height from deleted  
  declare ins_cursor cursor local forward_only static optimistic for    
    select batchcode, bquantity, depotid, productid, assqty,
      productdate, validdate, custom1, custom2, custom3, length, width, height from inserted

  open del_cursor
  open ins_cursor
  fetch next from del_cursor into @old_batchno, @old_quantity, @old_depotid, @old_productid, @old_assqty,
    @oproduceddate, @ovaliddate, @ocustom1, @ocustom2, @ocustom3, @olength, @owidth, @oheight
  fetch next from ins_cursor into @new_batchno, @new_quantity, @new_depotid, @new_productid, @new_assqty,
    @produceddate, @validdate, @custom1, @custom2, @custom3, @length, @width, @height
  
  while (@@fetch_status = 0)
  begin       
    update tt_bookbatch set beginqty = beginqty - @old_quantity,
        beginassqty = beginassqty - @old_assqty,
        assqty = assqty - @old_assqty,
        quantity = quantity - @old_quantity
    where batchcode = @old_batchno and productid = @old_productid and depotid = @old_depotid
      and productdate = @oproduceddate and validdate=@ovaliddate
      and custom1=@ocustom1 and custom2=@ocustom2 and custom3=@ocustom3 and length=@olength and width=@owidth and height=@oheight

    if (exists(select * from tt_bookbatch where productid = @new_productid and depotid = @new_depotid and batchcode = @new_batchno
      and productdate=@produceddate and validdate=@validdate
      and custom1=@custom1 and custom2=@custom2 and custom3=@custom3 and length=@length and width=@width and height=@height))
    begin
      update tt_bookbatch set beginqty = @new_quantity, beginassqty = @new_assqty,
        quantity = quantity + @new_quantity,
        assqty = assqty + @new_assqty
      where productid = @new_productid and depotid = @new_depotid and batchcode = @new_batchno 
        and productdate=@produceddate and validdate=@validdate
        and custom1=@custom1 and custom2=@custom2 and custom3=@custom3 and length=@length and width=@width and height=@height
    end
    else
       insert into tt_bookbatch
        (batchcode, depotid, productid, quantity, beginqty, assqty, beginassqty,
         productdate, validdate,
         custom1, custom2, custom3, length, width, height )
       values (@new_batchno,@new_depotid,@new_productid,@new_quantity,@new_quantity,@new_assqty,@new_assqty,
         @produceddate, @validdate,
         @custom1, @custom2, @custom3, @length, @width, @height)   
    fetch next from del_cursor into @old_batchno, @old_quantity, @old_depotid, @old_productid, @old_assqty,
      @oproduceddate, @ovaliddate, @ocustom1, @ocustom2, @ocustom3, @olength, @owidth, @oheight
    fetch next from ins_cursor into @new_batchno, @new_quantity, @new_depotid, @new_productid, @new_assqty,
      @produceddate, @validdate, @custom1, @custom2, @custom3, @length, @width, @height
  end
  
  close del_cursor
  close ins_cursor
  deallocate del_cursor
  deallocate ins_cursor
end

